package com.example.computer_exam_training_sys.mapper;

import com.example.computer_exam_training_sys.entity.DiscussionTopicInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface DiscussionTopicMapper {

    @Insert("INSERT INTO discussion_topics(user_id, question_id, title, content) " +
            "VALUES(#{userId}, #{questionId}, #{title}, #{content})")
    int insertTopic(DiscussionTopicInfo discussion_topics);


    @Delete("DELETE FROM discussion_topics WHERE topic_id = #{topicId}")
    int deleteTopicById(Integer topicId);

    @Select("SELECT * FROM discussion_topics WHERE topic_id = #{topicId}")
    DiscussionTopicInfo getTopicById(Integer topicId);

    @Select("SELECT * FROM discussion_topics")
    List<DiscussionTopicInfo> getTopicList();

    @Select("<script>" +
            "SELECT dt.*, u.username, q.content as question_content " +
            "FROM discussion_topics dt " +
            "LEFT JOIN users u ON dt.user_id = u.user_id " +
            "LEFT JOIN questions q ON dt.question_id = q.question_id " +
            "<where>" +
            "   <if test='topicId != null'> AND dt.topic_id = #{topicId} </if>" +
            "</where>" +
            " LIMIT #{offset}, #{size}" +
            "</script>")
    List<DiscussionTopicInfo> getTopicListByPage(@Param("topicId") Integer topicId,
                                                 @Param("offset") int offset,
                                                 @Param("size") int size);

    @Select("<script>" +
            "SELECT COUNT(*) FROM discussion_topics dt " +
            "<where>" +
            "   <if test='topicId != null'> AND dt.topic_id = #{topicId} </if>" +
            "</where>" +
            "</script>")
    int countTopics(@Param("topicId") Integer topicId);

    @Select("<script>" +
            "SELECT dt.*, tc.comment_id, tc.user_id AS comment_user_id, tc.content AS comment_content " +
            "FROM discussion_topics dt " +
            "LEFT JOIN topic_comments tc ON dt.topic_id = tc.topic_id " +
            "<where>" +
            "   <if test='topicId != null'> AND dt.topic_id = #{topicId} </if>" +
            "</where>" +
            "ORDER BY dt.topic_id, tc.comment_id" +
            "</script>")
    List<DiscussionTopicInfo> getTopicWithComments(@Param("topicId") Integer topicId);

    @Select("<script>" +
            "SELECT " +
            "    dt.topic_id, " +
            "    dt.title, " +
            "    dt.content AS topic_content, " +
            "    dt.create_time AS topic_create_time, " +
            "    tc.comment_id, " +
            "    tc.content AS comment_content, " +
            "    tc.create_time AS comment_create_time, " +
            "    tc.user_id AS comment_user_id " +
            "FROM " +
            "    discussion_topics dt " +
            "LEFT JOIN " +
            "    topic_comments tc ON dt.topic_id = tc.topic_id " +
            "WHERE " +
            "    dt.topic_id = #{topicId} " +
            "</script>")
    List<Map<String, Object>> getTopicWithCommentsDetails(@Param("topicId") Integer topicId);

    @Select("<script>" +
            "SELECT dt.*, u.username, q.content as question_content " +
            "FROM discussion_topics dt " +
            "LEFT JOIN users u ON dt.user_id = u.user_id " +
            "LEFT JOIN questions q ON dt.question_id = q.question_id " +
            "<where>" +
            "   <if test='topicId != null'> AND dt.topic_id = #{topicId} </if>" +
            "   <if test='title != null and title != \"\"'> AND dt.title LIKE CONCAT('%', #{title}, '%') </if>" +
            "</where>" +
            " LIMIT #{offset}, #{size}" +
            "</script>")
    List<DiscussionTopicInfo> searchTopicsByTopicIdOrTitle(@Param("topicId") Integer topicId,
                                                           @Param("title") String title,
                                                           @Param("offset") int offset,
                                                           @Param("size") int size);

    @Select("<script>" +
            "SELECT COUNT(*) FROM discussion_topics dt " +
            "<where>" +
            "   <if test='topicId != null'> AND dt.topic_id = #{topicId} </if>" +
            "   <if test='title != null and title != \"\"'> AND dt.title LIKE CONCAT('%', #{title}, '%') </if>" +
            "</where>" +
            "</script>")
    int countSearchTopics(@Param("topicId") Integer topicId, @Param("title") String title);

    @Select("<script>" +
            "SELECT dt.*, u.username, q.content AS question_content, c.course_name " +
            "FROM discussion_topics dt " +
            "LEFT JOIN users u ON dt.user_id = u.user_id " +
            "LEFT JOIN questions q ON dt.question_id = q.question_id " +
            "LEFT JOIN knowledge_points kp ON q.point_id = kp.point_id " +
            "LEFT JOIN courses c ON kp.course_id = c.course_id " +
            "<where> " +
            "   <if test='topicId != null'> AND dt.topic_id = #{topicId} </if> " +
            "   <if test='courseName != null and courseName != \"\"'> AND c.course_name = #{courseName} </if> " +
            "   <if test='keyword != null and keyword != \"\"'> " +
            "     AND (dt.title LIKE CONCAT('%', #{keyword}, '%') OR u.username LIKE CONCAT('%', #{keyword}, '%')) " +
            "   </if> " +
            "</where> " +
            "ORDER BY dt.create_time DESC " +
            "LIMIT #{offset}, #{size} " +
            "</script>")
    List<DiscussionTopicInfo> getTopicListWithCourseName(
            @Param("topicId") Integer topicId,
            @Param("courseName") String courseName,
            @Param("keyword") String keyword,
            @Param("offset") int offset,
            @Param("size") int size
    );

    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM discussion_topics dt " +
            "LEFT JOIN users u ON dt.user_id = u.user_id " +
            "LEFT JOIN questions q ON dt.question_id = q.question_id " +
            "LEFT JOIN knowledge_points kp ON q.point_id = kp.point_id " +
            "LEFT JOIN courses c ON kp.course_id = c.course_id " +
            "<where> " +
            "   <if test='topicId != null'> AND dt.topic_id = #{topicId} </if> " +
            "   <if test='courseName != null and courseName != \"\"'> AND c.course_name = #{courseName} </if> " +
            "   <if test='keyword != null and keyword != \"\"'> " +
            "     AND (dt.title LIKE CONCAT('%', #{keyword}, '%') OR u.username LIKE CONCAT('%', #{keyword}, '%')) " +
            "   </if> " +
            "</where> " +
            "</script>")
    int countTopicsWithCourseName(
            @Param("topicId") Integer topicId,
            @Param("courseName") String courseName,
            @Param("keyword") String keyword
    );

    @Select("SELECT c.course_name, COUNT(*) AS post_count " +
            "FROM discussion_topics dt " +
            "LEFT JOIN questions q ON dt.question_id = q.question_id " +
            "LEFT JOIN knowledge_points kp ON q.point_id = kp.point_id " +
            "LEFT JOIN courses c ON kp.course_id = c.course_id " +
            "WHERE dt.status = 1 " +
            "AND dt.create_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) " +  // 只统计最近一个月内的帖子
            "GROUP BY c.course_id " +
            "ORDER BY post_count DESC " +
            "LIMIT 2")
    List<Map<String, Object>> getTopCourses();

    @Select("SELECT dt.topic_id, dt.title " +
            "FROM discussion_topics dt " +
            "WHERE DATE(dt.create_time) = CURDATE() " + // 只查当天
            "ORDER BY dt.reply_count DESC " +
            "LIMIT 2")
    List<Map<String, Object>> getHotTopics();

    // DiscussionTopicMapper.java
    @Update("UPDATE discussion_topics SET view_count = view_count + 1 WHERE topic_id = #{topicId}")
    int increaseViewCount(Integer topicId);
}